package com.project.yunshitong.utils;

import static android.content.ContentValues.TAG;

import static com.project.yunshitong.utils.Tools.readFileSdcardFile;

import android.content.Context;
import android.content.Intent;
import android.net.Uri;
import android.os.Environment;
import android.text.TextUtils;
import android.util.Log;
import android.widget.Toast;

import com.project.yunshitong.bean.DepartmentBean;
import com.project.yunshitong.bean.GoodsInfo;
import com.project.yunshitong.bean.TypeBean;

import org.greenrobot.eventbus.EventBus;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.demo.Write;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * @author dmrfcoder
 * @date 2019/2/14
 */

public class ExcelUtil {

    private static WritableFont arial14font = null;

    private static WritableCellFormat arial14format = null;
    private static WritableFont arial10font = null;
    private static WritableCellFormat arial10format = null;
    private static WritableFont arial12font = null;
    private static WritableCellFormat arial12format = null;
    private final static String UTF8_ENCODING = "UTF-8";


    /**
     * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
     */
    private static void format() {
        try {
            arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
            arial14font.setColour(Colour.LIGHT_BLUE);
            arial14format = new WritableCellFormat(arial14font);
            arial14format.setAlignment(Alignment.CENTRE);
            arial14format.setBorder(Border.ALL, BorderLineStyle.THIN);
            arial14format.setBackground(Colour.VERY_LIGHT_YELLOW);

            arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            arial10format = new WritableCellFormat(arial10font);
            arial10format.setAlignment(Alignment.CENTRE);
            arial10format.setBorder(Border.ALL, BorderLineStyle.THIN);
            arial10format.setBackground(Colour.GRAY_25);

            arial12font = new WritableFont(WritableFont.ARIAL, 10);
            arial12format = new WritableCellFormat(arial12font);
            //对齐格式
            arial10format.setAlignment(Alignment.CENTRE);
            arial12format.setVerticalAlignment(VerticalAlignment.CENTRE);
            //设置边框
            arial12format.setBorder(Border.ALL, BorderLineStyle.THIN);
            arial12format.setAlignment(Alignment.CENTRE);
            arial12format.setVerticalAlignment(VerticalAlignment.CENTRE);

        } catch (WriteException e) {
            e.printStackTrace();
        }
    }


    /**
     * 初始化Excel表格
     *
     * @param filePath  存放excel文件的路径（path/demo.xls）
     * @param sheetName Excel表格的表名
     * @param colName   excel中包含的列名（可以有多个）
     */
    private static final int DATEA_PICKER_ID = 1;
    private static final int DATEB_PICKER_ID = 2;

    public static void initExcel(Context context, String sheetName, String[] colName) {
        format();
        WritableWorkbook workbook = null;
        try {
            ACache aCache = ACache.get(context);
            // 查找文件是否存在
            String fileName = aCache.getAsString("name") + "_EXCEL";
            String freqListTxTPath = Environment.getExternalStorageDirectory() + "/导出数据/" + fileName + ".xls";
            File file = new File(freqListTxTPath);
            if (file.exists()) {
                file.delete();
            }
            // 上级文件不存在，进行文件夹的创建
//            if (!file.exists()) { // 先根据路径创建文件夹
//                file.mkdirs();
//            }
            boolean b = file.createNewFile();
            Log.e(TAG, "initExcel: 创建文件b===================" + b);
            workbook = Workbook.createWorkbook(file);
            //设置表格的名字
            WritableSheet sheet = workbook.createSheet(sheetName, 0);
            //创建标题栏
//            sheet.addCell((WritableCell) new Label(0, 0, freqListTxTPath, arial14format));
//            for (int col = 0; col < colName.length; col++) {
//                sheet.addCell(new Label(col, 0, colName[col], arial10format));
//            }
            //设置行高
            sheet.setRowView(0, 340);
            workbook.write();
//            workbook.close();
        } catch (Exception e) {
            Log.e(TAG, "initExcel: 创建文件出错===========" + e.getLocalizedMessage());
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    Log.e(TAG, "initExcel: 创建文件出错1===========" + e.getLocalizedMessage());
                    e.printStackTrace();
                }
            }
        }
    }


    public static void writeTxtFreq(Context context, List<GoodsInfo> list) {
        ACache aCache = ACache.get(context);
        // 查找文件是否存在
        String fileName = aCache.getAsString("name") + "_RESULT";
        String freqListTxTPath = Environment.getExternalStorageDirectory() + "/导出数据/" + fileName + ".dat";
        File file = new File(freqListTxTPath);
        if (file.exists()) {
            file.delete();
        }
        // 如果不存在，创建文件，并写入内容
        if (!file.exists()) {
            Log.e(TAG, "writeTxtFreq: 文件不存在");
            // 获取字符串
//            ArrayList<String> arrayList = new ArrayList<>();
            String path = aCache.getAsString("info");
            String fileInfo = "";
            try {
                fileInfo = readFileSdcardFile(path);
            } catch (IOException e) {
                e.printStackTrace();
            }

//            arrayList.add("46000 200 50 2 true\n");
//            arrayList.add("46000 300 50 3 true\n");
            /*arrayList.add("#TITLE=部门盘点-税收科学研究所\n" +
                    "#FILEFMT=#KEY,BARCODE,S,18|#STATE,STATE,I,1|V1,BOMID,I,9|V2,DEPTID,I,12|V3,DEPTUSER,S,32|V4,DEPTNAME,S,32|V5,MODEL,S,32|V6,RESNAME,S,500|V7,SERIALNO,S,64|V8,PLACE,S,64|#CID_CHK,CID,S,12|#DID_CHK,DEPTID,S,12\n" +
                    "#SHOWFMT=#KEY,资产编号：,E|V3,使用人：,L|V6,资产名称:,L|V5,型  号：,L|V4,使用部门：,L|V7,序列号：,L|V8,摆放位置：,L|#CID_CHK,盘单位,H|#DID_CHK,盘部门,H\n" +
                    "#COUNT=22\n" +
                    "#SPLITE=9\n" +
                    "#INCLUDE=1\n" +
                    "#BILL=59504\n" +
                    "#DEPT=140\n" +
                    "#CDLIST=10000\t0\t国家税务总局广州市税务局机关\t140\t-1\t税收科学研究所\n");*/
            String first = fileInfo.substring(0, fileInfo.indexOf("20"));
            StringBuilder sb = new StringBuilder();
            sb.append(first);
            Log.e(TAG, "writeTxtFreq: 开始时间====" + System.currentTimeMillis());
//            Log.e(TAG, "writeTxtFreq: list.size()======"+list.size() );
            for (int index = 0; index < list.size(); index++) {
                EventBus.getDefault().post(new TypeBean(2, index));
//                Log.e(TAG, "writeTxtFreq: index============"+index );
//                Log.e(TAG, "writeTxtFreq: list.get(index)===========" + list.get(index));
                String con = "";
                if (index == (list.size() - 1)) {
                    con = fileInfo.substring(fileInfo.indexOf(list.get(index).getCode()));
                } else {
                    int indexx = fileInfo.indexOf(list.get(index).getCode());
                    int indexxx = fileInfo.indexOf(list.get(index + 1).getCode());
                    con = fileInfo.substring(indexx, indexxx);
                }
                //                Log.e(TAG, "writeTxtFreq: con========="+con );
//                Log.e(TAG, "writeTxtFreq: first========="+first );
//                con.indexOf("\t0\t");
//                if ("4".equals(list.get(index).getStatus())) {
                    con = con.replaceFirst("\t0\t", "\t" + "1" + "\t");
                /*} else {
                    String state = list.get(index).getStatus();
                    if (state.equals("3")) {
//                        con = con.replaceFirst("\t" + list.get(index).getOldDeptid() + "\t",
//                                "\t" + list.get(index).getDeptid() + "\t");
                        String start = con.substring(0, con.indexOf(list.get(index).getOldDeptid()) + list.get(index).getOldDeptid().length());
                        String end = con.substring(con.indexOf(list.get(index).getOldDeptid()) + list.get(index).getOldDeptid().length());
                        Log.e(TAG, "writeTxtFreq: Start====" + start + end);
                        end = end.replaceFirst(list.get(index).getOldDeptid(),
                                list.get(index).getDeptid());
                        con = start + end;
//                        String oldDepart = list.get(index).getOldDepartment();
//                        String newDepart = list.get(index).getDepartment();
////                        Log.e(TAG, "writeTxtFreq: 部门=========================" );
////                        Log.e(TAG, "writeTxtFreq: 旧部门======="+con.substring(0,con.indexOf(oldDepart)));
////                        Log.e(TAG, "writeTxtFreq: 新部门======="+con.substring(con.indexOf(oldDepart)+oldDepart.length()));
////                        Log.e(TAG, "writeTxtFreq: 部门=========================" );
//                        con = con.substring(0,con.indexOf(oldDepart))+newDepart+con.substring(con.indexOf(oldDepart)+oldDepart.length());;
                        state = "2";
                    }
                    con = con.replaceFirst("\t0\t", "\t" + state + "\t");
                }*/
//                Log.e(TAG, "writeTxtFreq: con.replaceFirst========"+con );
//                fileInfo = first + con;
                /*arrayList.add(getString(info.getCode()) + getString(info.getStatus()) + getString(info.getBomid()) + getString(info.getDeptid())
                        + getString(info.getUser()) + getString(info.getDepartment()) + getString(info.getModel())
                        + getString(info.getName()) + getString(info.getNo()) + getString(info.getAddress()) + getString(info.getUnit())
                        + getString(info.getInDepart()) + "\n");*/
                sb.append(con);
            }
            Log.e(TAG, "writeTxtFreq: 结束时间====" + System.currentTimeMillis());
//            arrayList.add(fileInfo);
//            for (String s : arrayList) {
//                sb.append(fileInfo);
//            }
            // 存储字符串
            String filename = fileName;
            String pathname = "导出数据";

            byte[] strByte = new byte[0];
            try {
                strByte = sb.toString().getBytes("gbk");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            int len = strByte.length;
            write2PhoneStorageDirectory(context, getDir(pathname), filename + ".dat", strByte, len);
        }
    }

    private static String getString(String content) {
//        if (content.length() < 30) {
//            int chinaLen = getChineseCount(content);
//            int notChinaLen = content.length() - chinaLen;
        int length = 2;//(30 - (chinaLen * 2 + notChinaLen)) / 2;
        for (int i = 0; i < length; i++) {
            content = " " + content;
        }
        for (int i = 0; i < length; i++) {
            content = content + " ";
        }
//        }
        return content;
    }


    /**
     * 得到字符串里中文的数量
     *
     * @param content
     * @return
     */
    public static int getChineseCount(String content) {
        int count = 0;
        String regEx = "[\u4e00-\u9fa5]";
        Pattern pattern = Pattern.compile(regEx);
        Matcher matcher = pattern.matcher(content);
        while (matcher.find()) {
            count++;
        }
        return count;
    }

    public static File write2PhoneStorageDirectory(Context context, String path, String fileName, byte[] data, int len) {
        Log.e(TAG, "write2PhoneStorageDirectory: path + fileName===" + path + fileName);
        File file = null;
        OutputStream output = null;
        try {
            file = new File(path + fileName);
            file.createNewFile();
//            file = createSDFile(path + "/" + fileName);
            output = new FileOutputStream(file, false);
            byte buffer[] = new byte[len];
            System.arraycopy(data, 0, buffer, 0, len);
            output.write(buffer);
            output.flush();

            Uri uri = Uri.fromFile(file);
            Intent intent = new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE, uri);
            context.sendBroadcast(intent);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (output != null)
                    output.close();
                else
                    Log.v(TAG, "output null");

            } catch (IOException e) {

                e.printStackTrace();
            }
        }
        return file;
    }

    public static String getDir(String pathname) {
        String path = Environment.getExternalStorageDirectory().toString();
        File dir = new File(path + File.separator + pathname);
        if (dir.exists()) {
            return dir.toString();
        } else {
            dir.mkdirs();
            return dir.toString();
        }
    }


    /**
     * 导出到excel表格
     */
    public static void writeTxtFreqExcel(Context context, List<GoodsInfo> list, HashMap<String, String> departmentList) {
        ACache aCache = ACache.get(context);
        // 查找文件是否存在
        String fileName = aCache.getAsString("name") + "_RESULT";
        String freqListTxTPath = Environment.getExternalStorageDirectory() + "/导出数据" + fileName + ".xls";
        Log.e(TAG, "writeTxtFreqExcel: freqListTxTPath=====" + freqListTxTPath);
        File file = new File(freqListTxTPath);
        if (file.exists()) {
            file.delete();
        }
        // 如果不存在，创建文件，并写入内容
        if (!file.exists()) {
            Log.e(TAG, "writeTxtFreq: 文件不存在");
            // 获取字符串
//            ArrayList<String> arrayList = new ArrayList<>();
            String path = aCache.getAsString("info");
            String fileInfo = "";
            try {
                fileInfo = readFileSdcardFile(path);
            } catch (IOException e) {
                e.printStackTrace();
            }

//            arrayList.add("46000 200 50 2 true\n");
//            arrayList.add("46000 300 50 3 true\n");
            /*arrayList.add("#TITLE=部门盘点-税收科学研究所\n" +
                    "#FILEFMT=#KEY,BARCODE,S,18|#STATE,STATE,I,1|V1,BOMID,I,9|V2,DEPTID,I,12|V3,DEPTUSER,S,32|V4,DEPTNAME,S,32|V5,MODEL,S,32|V6,RESNAME,S,500|V7,SERIALNO,S,64|V8,PLACE,S,64|#CID_CHK,CID,S,12|#DID_CHK,DEPTID,S,12\n" +
                    "#SHOWFMT=#KEY,资产编号：,E|V3,使用人：,L|V6,资产名称:,L|V5,型  号：,L|V4,使用部门：,L|V7,序列号：,L|V8,摆放位置：,L|#CID_CHK,盘单位,H|#DID_CHK,盘部门,H\n" +
                    "#COUNT=22\n" +
                    "#SPLITE=9\n" +
                    "#INCLUDE=1\n" +
                    "#BILL=59504\n" +
                    "#DEPT=140\n" +
                    "#CDLIST=10000\t0\t国家税务总局广州市税务局机关\t140\t-1\t税收科学研究所\n");*/
//            String first = fileInfo.substring(0, fileInfo.indexOf("200"));
            StringBuilder sb = new StringBuilder();
//            String title = "资产编码\t盘点状态\t部门ID\t部门名称\t使用人\t型号\t资产名称\t编号\t摆放位置\t使用单位       \t盘点部门         \n";
//            first = first + title;
//            sb.append(title);
            String title = "资产编码\t盘点状态\tBomId\t部门ID\t使用人\t部门名称\t型号\t资产名称\t编号\t摆放位置\t使用单位\t盘点部门\t\n";
            sb.append(title);
//            Log.e(TAG, "writeTxtFreq: 开始内容====" + sb);
            Log.e(TAG, "writeTxtFreq: 开始时间====" + System.currentTimeMillis());
//            Log.e(TAG, "writeTxtFreq: list.size()======"+list.size() );
            for (int index = 0; index < list.size(); index++) {
                EventBus.getDefault().post(new TypeBean(2, index));
//                Log.e(TAG, "writeTxtFreq: index============"+index );
//                Log.e(TAG, "writeTxtFreq: list.get(index)===========" + list.get(index));
                /**获取当前条码的数据*/
                String con = "";
                if (index == (list.size() - 1)) {
                    con = fileInfo.substring(fileInfo.indexOf(list.get(index).getCode()));
                } else {
                    int indexx = fileInfo.indexOf(list.get(index).getCode());
                    int indexxx = fileInfo.indexOf(list.get(index + 1).getCode());
                    con = fileInfo.substring(indexx, indexxx);
                }
                String kk = "";
                int length = list.get(index).getCode().length();
                for (int i = 0; i < 18 - length; i++) {
                    kk = kk + " ";
                }
//                if (list.get(index).getCode().length() == 10) {
                    con = con.replaceFirst(kk, "");
//                } else {
//                    con = con.replaceFirst("   ", "");
//                }
//                while (con.indexOf(" ")!=-1) {
//                    con = con.replace(" ", "");
////                    con = con.replace("\t", "");
//                }
                if ("4".equals(list.get(index).getStatus())) {
                    con = con.replaceFirst("\t0\t", "\t" + "已盘" + "\t");
                } else {
                    String state = list.get(index).getStatus();
                    if (state.equals("3")) {
//                        con = con.replaceFirst("\t" + list.get(index).getOldDeptid() + "\t",
//                                "\t" + list.get(index).getDeptid() + "\t");
                        String start = con.substring(0, con.indexOf(list.get(index).getOldDeptid()) + list.get(index).getOldDeptid().length());
                        String end = con.substring(con.indexOf(list.get(index).getOldDeptid()) + list.get(index).getOldDeptid().length());
                        Log.e(TAG, "writeTxtFreq: Start====" + start + end);
                        end = end.replaceFirst(list.get(index).getOldDeptid(),
                                list.get(index).getDeptid());
                        con = start + end;
                        state = "盘盈";
                    } else if (state.equals("0")) {
                        state = "未盘";
                    } else if (state.equals("1")) {
                        state = "已盘";
                    }
                    con = con.replaceFirst("\t0\t", "\t" + state + "\t");
                }
                con = con.replaceFirst("\t1\t", "\t" + "已盘" + "\t");
                con = con.replace("\t" + list.get(index).getDeptid() + "\t", "\t" + departmentList.get(list.get(index).getDeptid()) + "\t");
                con = con.replace("\t" + list.get(index).getUnit() + "\t", "\t" + departmentList.get(list.get(index).getUnit()) + "\t");
                con = con.replace("\t" + list.get(index).getUnit() + " ", "\t" + departmentList.get(list.get(index).getUnit()) + "");
                con = con.replace("\t" + list.get(index).getInDepart() + "\t", "\t" + departmentList.get(list.get(index).getInDepart()) + "\t");
                con = con.replace("\t" + list.get(index).getInDepart() + " ", "\t" + departmentList.get(list.get(index).getInDepart()) + "");
//                con = con.replace("\t"+list.get(index).getUser()+"\t", "\t" + departmentList.get(list.get(index).getUser()) + "\t");
                Log.e(TAG, "writeTxtFreqExcel: con===" + con);
                sb.append(con);
            }
            Log.e(TAG, "writeTxtFreq: 结束时间====" + System.currentTimeMillis());
//            Log.e(TAG, "writeTxtFreq: 结束内容====" + sb);
//            while (sb.indexOf(" ") != -1) {
//                sb = sb.replace(sb.indexOf(" "), sb.indexOf(" ")+1," ");
////                    con = con.replace("\t", "");
//            }
            // 存储字符串
            String filename = fileName;
            String pathname = "导出数据";

            byte[] strByte = new byte[0];
            try {
                strByte = "".getBytes("gbk");//sb.toString().getBytes("gbk");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            int len = strByte.length;
            File file1 = write2PhoneStorageDirectory(context, getDir(pathname), filename + ".xls", strByte, len);
            try {
                write(sb.toString(), file1);
            } catch (IOException e) {
                e.printStackTrace();
            } catch (WriteException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 同步操作，防止并发。
     *
     * @return
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
    public synchronized static String[] write(String con, File file)
            throws IOException, RowsExceededException, WriteException {

        // 文件路径
        // 判断文件是否存在，如果存在就不创建，追加，如果不存在则创建文件并追加。
        WritableWorkbook book = Workbook.createWorkbook(file);
        book.setProtected(false);
        // -- 第一个参数是Sheet名，第二个参数是Sheet下标
        // -- 下标是整数，只起标识作用，建立的时候会以create顺序建立，本例生成的EXCEL文件第一个Sheet是sheet1
        WritableSheet sheet = book.createSheet("盘点结果", 1);
        sheet.setColumnView(0, 20);//设置excel表格中对应列宽 （x，width） x表示列  y表示宽度
        sheet.setColumnView(1, 10);
        sheet.setColumnView(2, 15);
        sheet.setColumnView(3, 20);
        sheet.setColumnView(4, 20);
        sheet.setColumnView(5, 20);
        sheet.setColumnView(6, 20);
        sheet.setColumnView(7, 20);
        sheet.setColumnView(8, 20);
        sheet.setColumnView(9, 35);
        sheet.setColumnView(10, 30);
        sheet.setColumnView(11, 30);
        //设置自适应宽度的方法，这里对中文字段无效，标题必须为英文才行
        //CellView cellView = new CellView();
        //cellView.setAutosize(true); //设置自动大小
        //sheet.setColumnView(0, cellView);

        sheet.getSettings().setProtected(false);
        String[] title = {"资产编码", "盘点状态", "BomId", "部门ID", "部门名称",
                "使用人", "型号", "资产名称", "编号", "摆放位置", "使用单位", "盘点部门"};
        for (int i = 0; i < title.length; i++) {
            Label lable = new Label(i, 0, title[i]);
            sheet.addCell(lable);
        }
        // 初次创建，写入一行。
        List<String> list = Arrays.asList(con.split("\n"));
        for (int i = 0; i < list.size(); i++) {
            String[] infos = list.get(i).trim().split("\t");
            List<String> info = new ArrayList<>(Arrays.asList(infos));
            for (int s = 0; s < info.size(); s++) {
                Label lable = new Label(s, i, info.get(s));
                sheet.addCell(lable);
            }
        }
        // 每次写入数据时，写到最后一行。
        book.write();
        book.close();
        System.out.println("写入成功");
        return null;
    }

}
